<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hzb.erp.api.pc.course.mapper.CourseMapper">

    <select id="getInfo" resultType="com.hzb.erp.api.pc.course.pojo.CourseVO">
        SELECT t1.*,
        t2.name subject_name
        FROM course t1
        LEFT JOIN subject t2 ON t2.id = t1.subject_id
        <where>
            <if test="id !=null ">
                AND t1.id = #{id}
            </if>
            <if test="name != null and name != ''">
                AND t1.name = #{name}
            </if>
            AND t1.deleted != 1 AND t1.state = 1
        </where>
    </select>

    <select id="getList" resultType="com.hzb.erp.api.pc.course.pojo.CourseVO">
        SELECT t1.*,
        t2.name subject_name,
        (select count(1) from course_link where course_id = t1.id group by course_id) link_count,
        count(t4.id) sale_count,
        sum(t4.price) sale_amount
        <if test="param.withFavRate !=null and param.withFavRate ">
            , ( select avg(t2.score) * 20 from course_comment t2 where t2.course_id = t1.id) fav_rate
        </if>
        FROM course t1
        LEFT JOIN subject t2 ON t2.id = t1.subject_id
        left join shop_order_item t4 on t4.item_id = t1.id and t4.item_type = 1 and t4.order_id in (
            select id from shop_order where state in (1, 10)
        )
        <where>
            <if test="param.subjectId !=null ">
                AND t1.subject_id = #{param.subjectId}
            </if>
            <if test="param.recommend !=null ">
                AND t1.recommend = #{param.recommend}
            </if>
            <if test="param.linkId !=null ">
                AND t1.id in (select linked_id from course_link where course_id = #{param.linkId})
            </if>
            <if test="param.lessonType !=null ">
                AND t1.lesson_type = #{param.lessonType}
            </if>
            <if test="param.state != null and param.state.size()>0">
                AND t1.state in
                <foreach collection="param.state" item="id" index="index" open="(" close=")" separator=",">
                    #{id}
                </foreach>
            </if>
            <if test="param.name !=null and param.name != ''">
                AND t1.name like concat('%', #{param.name}, '%')
            </if>

            <if test="param.forSale !=null and param.forSale">
                AND t1.for_sale = 1 and ((t1.close_date is not null and t1.close_date >= CURDATE()) or t1.close_date is null)
            </if>
            AND t1.deleted != 1
        </where>
        group by t1.id
        order by t1.id desc
    </select>

</mapper>
